<?php
/*
	Contribution Name: Manufacturer Sales Report
	Contribution Version: 2.0

	Author Name: Robert Heath
	Author E-Mail Address: robert@rhgraphicdesign.com
	Author Website: http://www.rhgraphicdesign.com
	Donations: www.paypal.com
	Donations Email: robert@rhgraphicdesign

	Released under the GNU General Public License
*/

  require('includes/application_top.php');
   
   if (isset($HTTP_GET_VARS['start_date'])) {
    $start_date = $HTTP_GET_VARS['start_date'];
  } else {
    $start_date = date('Y-m-01');
  }

  if (isset($HTTP_GET_VARS['end_date'])) {
    $end_date = $HTTP_GET_VARS['end_date'];
  } else {
    $end_date = date('Y-m-d');
  }

	require(DIR_WS_CLASSES . 'currencies.php');
	$currencies = new currencies;

	// If no manufacturer is specified, build totals for all manufacturers
	if (empty($HTTP_GET_VARS['mID'])) {
		$total_sales = 0;
		$total_taxes = 0;
		$total_taxed_sales = 0;
		$total_quantity = 0;		
		$manufacturers_query_raw = "select manufacturers_id, manufacturers_name
										FROM " . TABLE_MANUFACTURERS . "
										ORDER BY manufacturers_name";
		$manufacturers_query = tep_db_query($manufacturers_query_raw);
		while ($manufacturers = tep_db_fetch_array($manufacturers_query)) {
			$products_query_raw = "select op.products_quantity, op.final_price, op.products_tax
										FROM " . TABLE_ORDERS_PRODUCTS . " AS op
										LEFT JOIN " . TABLE_PRODUCTS . " AS p ON op.products_id = p.products_id
										LEFT JOIN " . TABLE_MANUFACTURERS . " AS m on p.manufacturers_id = m.manufacturers_id
										LEFT JOIN " . TABLE_ORDERS . " AS o ON op.orders_id = o.orders_id
										WHERE o.date_purchased BETWEEN '" . $start_date . " 00:00:00' AND '" . $end_date . " 23:59:59'
										AND m.manufacturers_name = '" . str_replace("'","\'",$manufacturers['manufacturers_name']) . "'
										ORDER BY m.manufacturers_name";
			$products_query = tep_db_query($products_query_raw);
			while ($products = tep_db_fetch_array($products_query)) {
				$manufacturer_quantity = $manufacturer_quantity + $products['products_quantity'];
				$manufacturer_sales = $manufacturer_sales + ($products['final_price'] * $products['products_quantity']);
				$manufacturer_taxes = $manufacturer_taxes + ((($products['final_price'] + $products['products_tax']) * $products['products_quantity']) - ($products['final_price'] * $products['products_quantity']));				
				$manufacturer_taxed_sales = $manufacturer_taxed_sales + (($products['final_price'] + $products['products_tax']) * $products['products_quantity']);
			}
			if (($manufacturer_sales > 0) || ($manufacturer_quantity > 0)) {
				$manufacturersStats[$manufacturers['manufacturers_id']] = array($manufacturers['manufacturers_name'],$manufacturer_quantity,$currencies->format($manufacturer_sales),$currencies->format($manufacturer_taxes),$currencies->format($manufacturer_taxed_sales));
				$total_sales = $total_sales + $manufacturer_sales;
				$total_taxes = $total_taxes + $manufacturer_taxes;
				$total_taxed_sales = $total_taxed_sales + $manufacturer_taxed_sales;
				$total_quantity = $total_quantity + $manufacturer_quantity;
				
			}
			$manufacturer_sales = 0;
			$manufacturer_taxes = 0;
			$manufacturer_taxed_sales = 0;
			$manufacturer_quantity = 0;	
		}
		$manufacturerTotalsHeader = "<tr class=\"dataTableHeadingRow\">
				<td class=\"dataTableHeadingContent\">" . TABLE_HEADING_MANUFACTURERS_NAME . "</td>
				<td class=\"dataTableHeadingContent\" align=\"right\">" . TABLE_TOTAL_PRODUCTS . "</td>
				<td class=\"dataTableHeadingContent\" align=\"right\">" . TABLE_TOTAL_SALES . "</td>
				<td class=\"dataTableHeadingContent\" align=\"right\">" . TABLE_TOTAL_TAX . "</td>
				<td class=\"dataTableHeadingContent\" align=\"right\">" . TABLE_TOTAL_TAXED_SALES . "</td></tr>";
	} else {
	// If a manufacturer has been specified, build product totals for that manufacturer
		$manufacturers_query_raw = "select manufacturers_name FROM " . TABLE_MANUFACTURERS . " WHERE manufacturers_id = " . $HTTP_GET_VARS['mID'];
		$manufacturers_query = tep_db_query($manufacturers_query_raw);
		if ($manufacturers = tep_db_fetch_array($manufacturers_query)) {
			$manufacturersName = $manufacturers['manufacturers_name'];	
			$manufacturersId = $manufacturers['manufacturers_id'];	

			$total_quantity = 0;
			$total_sales = 0;		
			$total_taxed_sales = 0;	
				
			// Find individual products sold
			$man_products_query_raw = "select DISTINCT op.products_id FROM " . TABLE_ORDERS_PRODUCTS . " AS op
										LEFT JOIN " . TABLE_PRODUCTS . " AS p ON op.products_id = p.products_id
										LEFT JOIN " . TABLE_MANUFACTURERS . " AS m on p.manufacturers_id = m.manufacturers_id
										LEFT JOIN " . TABLE_ORDERS . " AS o ON op.orders_id = o.orders_id
										WHERE o.date_purchased BETWEEN '" . $HTTP_GET_VARS['start_date'] . " 00:00:00' AND '" . $HTTP_GET_VARS['end_date'] . " 23:59:59'
										AND m.manufacturers_id = " . $HTTP_GET_VARS['mID'] . "
										ORDER BY op.products_model";
			$man_products_query = tep_db_query($man_products_query_raw);
			while ($man_products = tep_db_fetch_array($man_products_query)) {	
				// Calculate sales totals for each product
				$products_query_raw = "select op.products_model, op.products_name, op.products_quantity, op.final_price, op.products_tax FROM " . TABLE_ORDERS_PRODUCTS . " AS op
											LEFT JOIN " . TABLE_PRODUCTS . " AS p ON op.products_id = p.products_id
											LEFT JOIN " . TABLE_ORDERS . " AS o ON op.orders_id = o.orders_id
											WHERE o.date_purchased BETWEEN '" . $HTTP_GET_VARS['start_date'] . " 00:00:00' AND '" . $HTTP_GET_VARS['end_date'] . " 23:59:59'
											AND op.products_id = " . $man_products['products_id'];
				$products_query = tep_db_query($products_query_raw);
				while ($products = tep_db_fetch_array($products_query)) {	
					$products_quantity = ($products_quantity + $products['products_quantity']);
					$final_price = ($final_price + ($products['final_price'] * $products['products_quantity']));
					$tax = $tax + ((($products['final_price'] + $products['products_tax']) * $products['products_quantity']) - ($products['final_price'] * $products['products_quantity']));				
					$taxed_price = ($taxed_price + (($products['final_price'] + $products['products_tax']) * $products['products_quantity']));
					$products_model = $products['products_model'];	
					$products_name = $products['products_name'];
				}
				
				$productsStats[$products_model] = array($products_name,$products_quantity,$currencies->format($final_price),$currencies->format($tax),$currencies->format($taxed_price));				
				$total_quantity = $total_quantity + $products_quantity;
				$total_taxes = ($total_taxes + $tax);	
				$total_sales = ($total_sales + $final_price);		
				$total_taxed_sales = ($total_taxed_sales + $taxed_price);		
				
				$products_quantity = 0;
				$final_price = 0;
				$tax = 0;
				$taxed_price = 0;
			}
			
			$productsTotalsHeader = "<tr class=\"dataTableHeadingRow\">
					<td class=\"dataTableHeadingContent\">" . TABLE_PRODUCT_MODEL . "</td>
					<td class=\"dataTableHeadingContent\">" . TABLE_PRODUCT_NAME . "</td>
					<td class=\"dataTableHeadingContent\" align=\"right\">" . TABLE_PRODUCT_QUANTITY . "</td>
					<td class=\"dataTableHeadingContent\" align=\"right\">" . TABLE_PRODUCT_REVENUE . "</td>
					<td class=\"dataTableHeadingContent\" align=\"right\">" . TABLE_PRODUCT_TAX . "</td>
					<td class=\"dataTableHeadingContent\" align=\"right\">" . TABLE_PRODUCT_TAXED_REVENUE . "</td></tr>";			
		} else $noManufacturer = true;
	}
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="<?php echo !$print ? 'includes/stylesheet.css' : 'includes/printer.css'; ?>">
<link rel="stylesheet" type="text/css" href="includes/javascript/spiffyCal/spiffyCal_v2_1.css">
<script language="JavaScript" src="includes/javascript/spiffyCal/spiffyCal_v2_1.js"></script>
<script language="javascript"><!--
  var startDate = new ctlSpiffyCalendarBox("startDate", "date_range", "start_date","btnDate1","<?php echo $start_date; ?>",scBTNMODE_CUSTOMBLUE);
  var endDate = new ctlSpiffyCalendarBox("endDate", "date_range", "end_date","btnDate2","<?php echo $end_date; ?>",scBTNMODE_CUSTOMBLUE);
//--></script>
<script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF" onLoad="SetFocus();">
<div id="spiffycalendar" class="text"></div>
<?php
	// set printer-friendly toggle
	(tep_db_prepare_input($HTTP_GET_VARS['print']=='yes')) ? $print=true : $print=false;
	// set inversion toggle
	
	// suppress left column for printer-friendly version
	if(!$print) {
?>
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->
<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>
    <!-- left_navigation //-->
    <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
        <?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
      </table></td>
    <!-- left_navigation_eof //-->
    <!-- body_text //-->
    <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">
        <tr>
          <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
              <tr>
                <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
                <td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
              </tr>
            </table></td>
        </tr>
        <tr>
          <td><!-- date range table -->
            <table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr class="main">
                <td align="left"><?php echo tep_draw_form('date_range','stats_manufacturers_sales.php' , '', 'get'); ?> <?php echo ENTRY_STARTDATE; ?> &nbsp;
                  <script language="javascript">startDate.writeControl(); startDate.dateFormat="yyyy-MM-dd";</script>
                  <?php echo ENTRY_TODATE; ?> &nbsp;
                  <script language="javascript">endDate.writeControl(); endDate.dateFormat="yyyy-MM-dd";</script>
                  <input type="submit" value="<?php echo ENTRY_SUBMIT; ?>">
                  </form></td>
                <td class="smallText" align="right"><a href="<?php  
				echo $_SERVER['PHP_SELF'] . "?" . $_SERVER['QUERY_STRING'] . "&print=yes";
				?>" target="print" title="<?php echo TEXT_BUTTON_REPORT_DESC; ?>"> <?php echo TEXT_BUTTON_REPORT_PRINT; ?></a> </td>
              </tr>
              <tr class="main">
                <td class="main" align="left"><?php echo HEADING_TITLE_PERIOD . ": " . date("M j, Y",strtotime($start_date)) .  " - " . date("M j, Y",strtotime($end_date)); ?> </td>
                <td class="main" align="left">Filter: <?php echo empty($HTTP_GET_VARS['mID']) ? "All Manufacturers" : $manufacturersName; ?></td>
              </tr>
            </table>
            <!-- end date range table -->
            <table border="0" width="100%" cellspacing="0" cellpadding="0">
              <tr>
                <td valign="top">
				<?php if (empty($HTTP_GET_VARS['mID'])) { ?>
                  <!-- screen all manufacturer table -->
                  <table border="0" width="100%" cellspacing="0" cellpadding="2">
                    <?php
		echo $manufacturerTotalsHeader; 
		foreach ($manufacturersStats as $manufacturersId => $stats) {
	  ?>
                    <tr class="dataTableRow" onMouseOver="rowOverEffect(this)" onMouseOut="rowOutEffect(this)" onClick="document.location.href='<?php echo tep_href_link(FILENAME_STATS_MANUFACTURERS, 'mID=' . $manufacturersId . "&start_date=$start_date&end_date=$end_date", 'NONSSL'); ?>'">
                      <td class="dataTableContent"><?php echo $stats[0]; ?></td>
                      <td class="dataTableContent" align="right"><?php echo $stats[1]; ?></td>
                      <td class="dataTableContent" align="right"><?php echo $stats[2]; ?>&nbsp;</td>
                      <td class="dataTableContent" align="right"><?php echo $stats[3]; ?>&nbsp;</td>
                      <td class="dataTableContent" align="right"><?php echo $stats[4]; ?>&nbsp;</td>
                    </tr>
                    <?php	
		}	 
?>
              <tr>
                <td class="dataTableContent" align="right">&nbsp;</td>
                <td class="dataTableContent" align="right"><strong><?php echo $total_quantity; ?></strong></td>
                <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_sales); ?></strong></td>
                <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_taxes); ?></strong></td>
                <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_taxed_sales); ?></strong></td>
              </tr>
                  </table>
                  <!-- end screen all manufacturer table -->
                </td>
              </tr>
            </table>
            <?php 
	} else {
		if (!$noManufacturer) {	
?>
            <!-- screen manufacturer table -->
            <table border="0" width="100%" cellspacing="0" cellpadding="2">
              <?php			  
			echo $productsTotalsHeader;
			foreach ($productsStats as $productsId => $stats) {
?>
              <tr class="dataTableRow">
                <td class="dataTableContent"><?php echo $productsId; ?></td>
                <td class="dataTableContent"><?php echo $stats[0]; ?></td>
                <td class="dataTableContent" align="right"><?php echo $stats[1]; ?></td>
                <td class="dataTableContent" align="right"><?php echo $currencies->format($stats[2]); ?></td>
                <td class="dataTableContent" align="right"><?php echo $currencies->format($stats[3]); ?></td>
                <td class="dataTableContent" align="right"><?php echo $currencies->format($stats[4]); ?></td>
              </tr>
              <?php
				}
?>
              <tr>
                <td class="dataTableContent" colspan="2" align="right">&nbsp;</td>
                <td class="dataTableContent" align="right"><strong><?php echo $total_quantity; ?></strong></td>
                <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_sales); ?></strong></td>
                <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_taxes); ?></strong></td>
                <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_taxed_sales); ?></strong></td>
              </tr>
            </table>
            <!-- end screen manufacturer table -->
            <?php  		
		} else echo "<h3>" . TEXT_NOT_FOUND . "</h3>";
	}
?>
          </td>
        </tr>
      </table></td>
    <!-- body_text_eof //-->
  </tr>
</table>
<!-- body_eof //-->
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<?php  }?>
<?php if ($print) {
?>
<!-- body_text //-->
<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr>
  <td>
  <table border="0" width="100%" cellspacing="0" cellpadding="0">
  <tr>
    <td class="pageHeading"><?php echo STORE_NAME; ?> </td>
  </tr>
  <tr>
  <td>
  <table>
  <tr>
    <td class="smallText"><?php echo HEADING_TITLE_REPORTED . ": "; ?></td>
    <td></td>
    <td class="smallText" align="left"><?php echo date(ltrim(TEXT_REPORT_DATE_FORMAT)); ?></td>
  </tr>
  </tr>
  <tr>
    <td class="smallText" align="left"><?php echo HEADING_TITLE_PERIOD . ": ";  ?></td>
    <td width="8">&nbsp;</td>
    <td class="smallText" align="left"><?php echo date("M j, Y",strtotime($start_date)) .  " - " . date("M j, Y",strtotime($end_date)); ?> </td>
  </tr>
  <table>
    </td>
    
    <td></td>
    </tr>
    <table border="0" width="100%" cellspacing="0" cellpadding="0">
      <tr>
        <td valign="top"><?php if (empty($HTTP_GET_VARS['mID'])) { ?>
          <table border="0" width="100%" cellspacing="0" cellpadding="2">
            <?php
		echo $manufacturerTotalsHeader;
		foreach ($manufacturersStats as $manufacturersId => $stats) {
	  ?>
            <tr class="dataTableRow">
              <td class="dataTableContent"><?php echo $stats[0]; ?></td>
              <td class="dataTableContent" align="right"><?php echo $stats[1]; ?></td>
              <td class="dataTableContent" align="right"><?php echo $stats[2]; ?>&nbsp;</td>
              <td class="dataTableContent" align="right"><?php echo $stats[3]; ?>&nbsp;</td>
              <td class="dataTableContent" align="right"><?php echo $stats[4]; ?>&nbsp;</td>
            </tr>
            <?php	
		}	
?>
              <tr>
                <td class="dataTableContent" align="right">&nbsp;</td>
                <td class="dataTableContent" align="right"><strong><?php echo $total_quantity; ?></strong></td>
                <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_sales); ?></strong></td>
                <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_taxes); ?></strong></td>
                <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_taxed_sales); ?></strong></td>
              </tr>
          </table>
          <?php 
	} else {
		if (!$noManufacturer) {
?>
          <table border="0" width="100%" cellspacing="0" cellpadding="2">
            <tr>
              <td class="main" colspan="5"><h3><?php echo $manufacturersName; ?></h3></td>
            </tr>
            <?php			  
			echo $productsTotalsHeader;
			foreach ($productsStats as $productsId => $stats) {
?>
            <tr class="dataTableRow">
              <td class="dataTableContent"><?php echo $productsId; ?></td>
              <td class="dataTableContent"><?php echo $stats[0]; ?></td>
              <td class="dataTableContent" align="right"><?php echo $stats[1]; ?></td>
              <td class="dataTableContent" align="right"><?php echo $currencies->format($stats[2]); ?></td>
              <td class="dataTableContent" align="right"><?php echo $currencies->format($stats[3]); ?></td>
              <td class="dataTableContent" align="right"><?php echo $currencies->format($stats[4]); ?></td>              
            </tr>
            <?php
				}
?>
            <tr>
              <td class="dataTableContent" colspan="2" align="right">&nbsp;</td>
              <td class="dataTableContent" align="right"><strong><?php echo $total_quantity; ?></strong></td>
              <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_sales); ?></strong></td>
              <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_taxes); ?></strong></td>              
              <td class="dataTableContent" align="right"><strong><?php echo $currencies->format($total_taxed_sales); ?></strong></td>
            </tr>
          </table>
          <?php  		
		} else echo "<h3>" . TEXT_NOT_FOUND . "</h3>";
	}
?>
        </td>
      </tr>
    </table>
    </td>
    
    <!-- body_text_eof //-->
    </tr>
    
  </table>
  <?php } ?>
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>
